'''
Author:thinktanker thinktanker@163.com
Date:2024-01-26 11:43:56
LastEditors: duliang thinktanker@163.com
LastEditTime: 2024-07-16 14:34:48
FilePath:
Description:
'''
# from multiprocessing import Process
from flask_cors import CORS
from flask import Flask, request, Response, abort, jsonify
# redirect, render_template, send_file, send_from_directory, url_for
import time
import sqlite3
from threading import Thread
import os
from gevent import pywsgi
# import requests
# import json
from datetime import timezone
import base64
# 自定义函数
from getipaddress import *
from getgkdb import *
from getsq import *
from mqttser import *
from ocr import *

os.system('title 接收中控室数据')
print("启动服务器")
app = Flask(__name__)
CORS(app, supports_credentials=True)  # 允许跨域
flag_time = 0
flag_time0 = 0
flag_time1 = 0
flag_time10 = 0
time_get_new = 10
# 判断中控接受是否超时，客户端是否在线
zhongkong_time = time.time()
zhongkong2_time = time.time()
# 判断是否有新的消息
newcheck = 0
# 是否mqtt发布
publish_flag = False
# 发布名称，作为重要标记
publish_name = ''
publish_type = None


def watchdog(watchdog_min=10):
    global zhongkong_time
    global zhongkong2_time
    print('watchdog启动 超时时间：', watchdog_min, '分钟')
    while True:
        cur_time = time.time()
        if cur_time - zhongkong_time > watchdog_min * 60:
            print(f"\033[31;4m{time.time()} 一站中控 未收到数据，重启服务\033[0m")
            sendTextToWxMsg(text='一站在线异常，需要排查')
            zhongkong_time = cur_time
            # copy_file('./image/com_lost.png', './image/1_zhan_status.png')
            with open('./data/check.json', 'r', encoding='utf-8') as f:
                j = json.load(f)
                j["1"] = 2
            with open('./data/check.json', 'w', encoding='utf-8') as f:
                json.dump(j, f)
        elif cur_time - zhongkong2_time > watchdog_min * 60:
            print(f"\033[31;4m{time.time()} 二站中控 未收到数据，重启服务\033[0m")
            sendTextToWxMsg(text='二站在线异常，需要排查')
            zhongkong2_time = cur_time
            # copy_file('./image/com_lost.png', './image/2_zhan_status.png')
            with open('./data/check.json', 'r') as f:
                j = json.load(f)
                j["2"] = 2
            with open('./data/check.json', 'w') as f:
                json.dump(j, f)
        time.sleep(1)


def saveLog(newlog, newtime, insert=True):
    '''
    保存log信息
    '''
    newlog = newlog.replace('机13', '机Ia').replace('1a', 'Ia').replace(
        'QQ', 'Q0').replace('”', '')
    conn = sqlite3.connect('./data/hisdata.db')
    cur = conn.cursor()
    if insert:
        sql = f'''INSERT INTO "history" ("xin", "shijian") VALUES ('{newlog}', {int(newtime)})'''
    else:
        sql = f'''UPDATE "history" SET "zhuijia"='{newlog}' WHERE ("shijian"={int(newtime)});'''
    cur.execute(sql)
    conn.commit()
    cur.close()
    conn.close()


# # 列表中存储被禁止的 IP 地址
# banned_ips = ['192.168.1.1', '::1']  # 示例 IP，根据需要替换或添加
# 列表中存储被允许的 IP 地址
# allowed_ips = [
#     '192.168.3.163', '223.68.11.42', '192.168.15.101', '218.93.240.126'
# ]  # 示例 IP，根据需要替换或添加


def save_ip(ip, ban):
    try:
        conn = sqlite3.connect('./data/users.db')
        cur = conn.cursor()
        sql = f'''SELECT count FROM "ip" WHERE "ip"="{ip}"'''
        rs = conn.execute(sql)
        rf = rs.fetchone()
        # print(rf)
        if rf:
            count = rf[0]
            sql = f'''UPDATE "ip" SET "count"={count+1},"last_time"={int(time.time())},"ban"={ban} WHERE "ip"="{ip}"'''
            cur.execute(sql)
            conn.commit()
        else:
            ip_address = get_ip_address([ip])
            if ip_address:
                ip_address = ip_address[0]
            sql = f'''INSERT INTO "ip" ("ip", "count","time","last_time","ban","address") VALUES ("{ip}", 1,{int(time.time())},{int(time.time())},{ban},"{ip_address}")'''
            cur.execute(sql)
            conn.commit()
    except Exception as e:
        print(e)
    finally:
        conn.close()


@app.before_request
def check_ip():
    ip = request.remote_addr
    with open('./data/config.json', 'r', encoding='utf-8') as f:
        data = json.load(f)
    allowed_ips = data["allowed_ips"]  # 读取JSON文件
    banIP = ip in allowed_ips
    save_ip(ip, banIP)
    if not banIP:
        abort(403, 'Your IP address has been banned.')


@app.route('/zhongkong', methods=['GET', 'POST'])
def zhongkong():
    global zhongkong_time
    if request.method == 'GET':
        return request.remote_addr
    elif request.method == 'POST':
        # recv_data = request.data.decode("utf8", "ignore")
        recv_time = time.time()
        zhongkong_time = recv_time
        recv_data = request.data
        print(
            f"\033[32;4m收到→{request.remote_addr}← 字节数:{len(recv_data)}\033[0m")
        print(f"\033[32m{ recv_data}\033[0m")
        try:
            th2 = Thread(target=judge,
                         args=(recv_time, recv_data, 1),
                         daemon=True)
            th2.start()
            return "done"
        except Exception as e:
            return e


@app.route('/zhongkong2', methods=['GET', 'POST'])
def zhongkong2():
    global zhongkong2_time
    if request.method == 'GET':
        return request.remote_addr
    elif request.method == 'POST':
        recv_time = time.time()
        zhongkong2_time = recv_time
        recv_data = request.data
        print(
            f"\033[32;4m收到→{request.remote_addr}← 字节数:{len(recv_data)}\033[0m")
        # recv_data = request.data.decode("utf8", "ignore")
        print(f"\033[32m{ recv_data}\033[0m")
        try:
            th2 = Thread(target=judge,
                         args=(time.time(), recv_data, 2),
                         daemon=True)
            th2.start()
            return "done"
        except Exception as e:
            return e


@app.route('/ip', methods=['GET'])
def ip():
    if request.method == 'GET':
        return request.remote_addr


# 小程序用的工控接口
@app.route('/gongkong', methods=['POST'])
def gongkong():
    # if request.method == 'GET':
    #     gcnum = request.args.get('gcnum')
    #     if gcnum == "0":
    #         return getgq_1zhan()
    #     elif gcnum == '1':
    #         return getgq_2zhan()
    #     else:
    #         return 'error'
    if request.method == 'POST':
        data = request.get_json()
        # print(data)
        if not data:
            return jsonify({"error": "No JSON data received"}), 400
        gcnum = data.get('gcnum')
        openid = data.get('openid')
        th = Thread(target=updateUser, args=(openid, ))
        th.daemon = True
        th.start()
        if gcnum is None:
            return jsonify({"error": "Missing 'gcnum' in JSON data"}), 400
        with open("./data/config.json", "r") as f:
            config = json.load(f)
        plus_time = int(config['plus_time']) * 60
        past_time = int(config['past_time']) * 60 * 60
        if int(gcnum) == 0:
            return jsonify({
                "respdata":
                getgq_1zhan(plus_time=plus_time, past_time=past_time)
            })
        elif int(gcnum) == 1:
            return jsonify({
                "respdata":
                getgq_2zhan(plus_time=plus_time, past_time=past_time)
            })
        else:
            return 'error'
        # response_data = {"received_code": getgq_1zhan()}
        # print(response_data)
        # return jsonify(response_data)


# 小程序用的工控接口
@app.route('/gkimg', methods=['GET'])
def gkimg():
    global flag_time0
    global flag_time1
    global flag_time10
    if request.method == 'GET':
        gcnum = request.args.get('gcnum')
        if gcnum == "0":
            gctime = request.args.get('t')
            if gctime:
                gctime = int(gctime)
                # if gctime - flag_time0 > time_get_new:
                #     response = Response(getgq_1zhan_image(),
                #                         mimetype='image/png')
                #     flag_time0 = gctime
                # else:
                with open('./image/1zhan.png', 'rb') as f:
                    response = Response(f.read(), mimetype='image/png')
                return response
        elif gcnum == '1':
            gctime = request.args.get('t')
            if gctime:
                gctime = int(gctime)
                # if gctime - flag_time1 > time_get_new:
                #     response = Response(getgq_2zhan_image(),
                #                         mimetype='image/png')
                #     flag_time1 = gctime
                # else:
                with open('./image/2zhan.png', 'rb') as f:
                    response = Response(f.read(), mimetype='image/png')
                return response
            else:
                return '缺少参数'
        elif gcnum == '10':
            gctime = request.args.get('t')
            if gctime:
                gctime = int(gctime)
                if gctime - flag_time10 > 60:
                    image1, image2 = getnydh_2zhan_8image()
                    if image1:
                        response10 = Response(image1, mimetype='image/png')
                        # response2 = Response(image2, mimetype='image/png')
                        flag_time10 = gctime
                    else:
                        with open('./image/temp.png', 'rb') as f:
                            response10 = Response(f.read(),
                                                  mimetype='image/png')
                else:
                    with open('./image/2znydhb.png', 'rb') as f:
                        response10 = Response(f.read(), mimetype='image/png')
                return response10
        elif gcnum == '11':
            gctime = request.args.get('t')
            with open('./image/2zsq.png', 'rb') as f:
                response11 = Response(f.read(), mimetype='image/png')
            return response11
        else:
            return 'error'


@app.route('/wdimg', methods=['GET'])
def wdimg():
    '''
    获取温度图片
    '''
    global flag_time
    if request.method == 'GET':
        gcnum = request.args.get('gcnum')
        if gcnum == "0" or gcnum == "1":
            gctime = request.args.get('t')
            if gctime:
                gctime = int(gctime)
                with open(f'./image/#{int(gcnum)+1}zwd_th.png', 'rb') as f:
                    response = Response(f.read(), mimetype='image/png')
                return response
    return 'error'


@app.route('/indeximg', methods=['GET'])
def indeximg():
    '''
    获取首页简单信息图片
    '''
    if request.method == 'GET':
        src = request.args.get('src')
        if src == "1" or src == "2":
            with open(f'./image/{src}_station_main_image.png', 'rb') as f:
                response = Response(f.read(), mimetype='image/png')
            return response
        # elif src == "2":
        #     with open(f'./image/2_station_main_image.png', 'rb') as f:
        #         response = Response(f.read(), mimetype='image/png')
        #     return response
        else:
            with open(f'./image/temp.png', 'rb') as f:
                response = Response(f.read(), mimetype='image/png')
            return response
    return 'error'


@app.route('/com', methods=['GET'])
def com():
    '''
    获取通信状态
    '''
    if request.method == 'GET':
        gcnum = request.args.get('gcnum')
        if gcnum:
            # with open('./data/check.json', 'r') as f:
            #     j = json.load(f)
            #     j[gcnum] = 1
            if gcnum == '1' or gcnum == '2':
                with open(f'./image/{gcnum}_zhan_status.png', 'rb') as f:
                    response = Response(f.read(), mimetype='image/png')
                return response
            else:
                # gctime = request.args.get('t')
                # if gctime:
                #     gctime = int(gctime)
                with open(f'./image/com_lost.png', 'rb') as f:
                    response = Response(f.read(), mimetype='image/png')
                return response
        # return 'ok'


@app.route('/qs', methods=['GET'])
def qs():
    if request.method == 'GET':
        gcname = request.args.get('gcname')
        qs_png_bytes = getqs(gcname)
        return Response(qs_png_bytes, mimetype="image/png")
    else:
        return 'error'


@app.route('/detail', methods=['POST'])
def detail():
    '''
    获取数据库内对应id的详细信息
    '''
    if request.method == 'POST':
        data = request.get_json()
        try:
            # 数据库的对应id，hisdata hisdata2
            dataset_id = data.get('id')
            # 数据库内对应的name,1,2,3,4,35kv,zb等
            gcname = data.get('n')
            # 数据库内列名
            parameter = data.get('p')
            result = get_detail_from_db(station=dataset_id,
                                        n=gcname,
                                        p=parameter)
            return jsonify({"respdata": result})
        except Exception as e:
            print(e)
            return 'error'
    else:
        return 'error'


@app.route('/login', methods=['POST'])
def login():
    if request.method == 'POST':
        data = request.get_json()
        if not data:
            return jsonify({"error": "No JSON data received"}), 400
        code = data.get('code')
        user_name = data.get('userName')
        if not code:
            return jsonify({"error": "Missing 'code' in JSON data"}), 400
        response_data = {"received_code": code}
        print(response_data)
        if not user_name:
            user_name = '未知用户'
        user_info = code2session(code, user_name)
        if user_info:
            print(f"User OpenID: {user_info, user_name}")
        else:
            print("Failed to get user info.")
        return jsonify(user_info), 200

    else:
        return '请求方法错误'


@app.route('/getUserInfo', methods=['POST'])
def getUserInfo():
    if request.method == 'POST':
        code = request.json.get('code')
        encrypted_data = request.json.get('encryptedData')
        iv = request.json.get('iv')

        if not all([code, encrypted_data, iv]):
            return jsonify({"error": "缺少必要参数"}), 400

        # 假设你已经有方法获取openid和session_key，这里简化处理
        # 应该从前端传递的code再次调用get_openid_session_key来获取，这里直接使用假设值
        session_key = '+sszKAVsA7th0LlLJeoUOg=='

        # 解密用户信息
        user_info = decrypt_user_info(session_key, encrypted_data, iv)
        if user_info is None:
            return jsonify({"error": "解密失败"}), 500

        # 根据需求处理user_info，比如保存到数据库
        print(user_info)
        return jsonify(user_info), 200


@app.route('/admin', methods=['POST'])
def admin():
    '''
    test页面测试的post获得 orderID
    '''
    if request.method == 'POST':
        data = request.get_json()
        if not data:
            return jsonify({"error": "No JSON data received"}), 400
        orderID = data.get('id')
        value = data.get('value')
        print(orderID, value)
        # orderID 为纯数字时对应的时小程序管理员界面顶部导航index
        if orderID == 0:
            with open('./data/config.json', 'r') as f:
                config_data = json.load(f)
                allowed_ids = config_data['allowed_ids']
            with open('./data/config.json', 'r') as f:
                config_data = json.load(f)
                allowed_ips1 = config_data['allowed_ips']
            with open(r'D:\myGitee\dul-text-recognition\allowed_ips.json',
                      'r') as f:
                config_data = json.load(f)
                allowed_ips2 = config_data['allowed_ips']
            return jsonify({
                "ips1": allowed_ips1,
                'ips2': allowed_ips2,
                'ids': allowed_ids
            }), 200
        elif orderID == 1:
            conn = sqlite3.connect('./data/users.db')
            sqltxt = f"SELECT id,ip,address,time,last_time,count,ban FROM ip order by last_time desc limit 50"
            rs = conn.execute(sqltxt)
            result = rs.fetchall()
            conn.close()
            return jsonify({"visitors": result}), 200
            # return jsonify({"error": "orderID is 1"}), 400
        elif orderID == 2:
            conn = sqlite3.connect('./data/users.db')
            sqltxt = f"SELECT id,username,count,lasttime,allowed,time FROM users order by lasttime desc"
            rs = conn.execute(sqltxt)
            result = rs.fetchall()
            conn.close()
            return jsonify({"users": result}), 200
        elif orderID == 3:
            session = requests.Session()
            headers = {
                'User-Agent':
                'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0.0.0 Safari/537.36 Edg/126.0.0.0',
            }
            resp = session.post(url='http://localhost:18083/api/v5/login',
                                headers=headers,
                                json={
                                    "username": "admin",
                                    "password": "821101cCW@"
                                })
            # print(resp.json())
            token = resp.json()['token']
            headers['Authorization'] = f'Bearer {token}'
            resp = session.get(
                url=
                'http://localhost:18083/api/v5/clients?conn_state=connected&limit=20&page=1&fields=clientid,username,connected,ip_address,port,keepalive,clean_start,expiry_interval,connected_at',
                headers=headers,
            )
            resp_json = resp.json()
            clients_list = []
            for client in resp_json['data']:
                # client['connected_at']
                time_str = client['connected_at']
                # print(time_str)
                dt_obj = datetime.fromisoformat(time_str.replace(
                    "Z", "+00:00"))
                timestamp = dt_obj.replace(tzinfo=timezone.utc).timestamp()
                connected_at = time.strftime("%Y-%m-%d %H:%M:%S",
                                             time.localtime(timestamp))
                clients_list.append([client['clientid'], connected_at])
                # print(client['clientid'])
                # print(client['connected_at'])
                # print(client['connected'])

            return jsonify({"mqttClients": clients_list}), 200
        elif orderID == 4:
            return jsonify({"error": "orderID is not 0,1,2,3"}), 400
        # 导航外其他的查询等
        else:
            # 包含 _ 为ip
            if "_" in str(orderID):
                '''
                修改IP的
                '''
                order_list = str(orderID).split("_")
                if len(order_list) > 2:
                    old_value = order_list[0]
                    order_flag = int(order_list[1])
                    json_path = int(order_list[2])
                    if json_path == 1:
                        json_path = r'./data/config.json'
                    elif json_path == 2:
                        json_path = r'D:\myGitee\dul-text-recognition\allowed_ips.json'
                    if order_flag == 1:
                        '''
                        delete,删除IP
                        '''
                        with open(json_path, 'r', encoding='utf-8') as f:
                            config_data = json.load(f)
                            allowed_ips = config_data[
                                "allowed_ips"]  # 读取JSON文件
                            allowed_ids = config_data[
                                "allowed_ids"]  # 读取JSON文件
                            # print(allowed_ips)
                            if old_value in allowed_ips:
                                allowed_ips.remove(old_value)
                                config_data["allowed_ips"] = allowed_ips
                            elif old_value in allowed_ids:
                                allowed_ids.remove(old_value)
                                config_data["allowed_ids"] = allowed_ids
                            # 输出修改后的JSON文件
                            # print(data)
                            with open(json_path, 'w', encoding='utf-8') as f:
                                json.dump(config_data,
                                          f,
                                          ensure_ascii=False,
                                          indent=4)
                        return jsonify({"done": "deleted"}), 200
                if value:
                    new_value = value
                    if order_flag == 0:
                        '''
                        update,更新IP
                        '''
                        with open(json_path, 'r', encoding='utf-8') as f:
                            config_data = json.load(f)
                            allowed_ips = config_data[
                                "allowed_ips"]  # 读取JSON文件
                            allowed_ids = config_data[
                                "allowed_ids"]  # 读取JSON文件
                            # print(allowed_ips)
                            if old_value in allowed_ips:
                                allowed_ips.remove(old_value)
                                allowed_ips.append(new_value)
                                config_data["allowed_ips"] = allowed_ips
                                config_data[
                                    "allowed_ips"] = allowed_ips  # 修改JSON文件
                            else:
                                for allowed_id in allowed_ids:
                                    if old_value == allowed_id['id']:
                                        allowed_ids.remove(allowed_id)
                                        config_data[
                                            "allowed_ids"] = allowed_id  # 修改JSON文件
                            # 输出修改后的JSON文件
                            # print(data)
                            with open(json_path, 'w', encoding='utf-8') as f:
                                json.dump(config_data,
                                          f,
                                          ensure_ascii=False,
                                          indent=4)
                            return jsonify({"done": "updated"}), 200

                    elif order_flag == 2:
                        '''
                        add,增加IP
                        '''
                        with open(json_path, 'r', encoding='utf-8') as f:
                            config_data = json.load(f)
                            allowed_ips = config_data[
                                "allowed_ips"]  # 读取JSON文件
                            # print(allowed_ips)
                            allowed_ips.append(new_value)
                            config_data[
                                "allowed_ips"] = allowed_ips  # 修改JSON文件
                            # 输出修改后的JSON文件
                            # print(data)
                            with open(json_path, 'w', encoding='utf-8') as f:
                                json.dump(config_data,
                                          f,
                                          ensure_ascii=False,
                                          indent=4)
                        return jsonify({"done": "added"}), 200
                return jsonify({"done": "nothing"}), 200
            # 包含 ~ 为 用户
            elif "~" in str(orderID) and value:
                '''
                修改用户名称
                '''
                order_list = str(orderID).split("~")
                _id = order_list[0]
                print(_id, value)
                conn = sqlite3.connect('./data/users.db')
                cur = conn.cursor()
                cur.execute('''UPDATE users SET username=? WHERE id=?''',
                            (value, _id))
                conn.commit()
                conn.close()
            elif "!" in str(orderID) and value:
                '''
                修改ip对应备注
                '''
                order_list = str(orderID).split("!")
                _id = order_list[0]
                print('修改ip对应备注', _id, value)
                conn = sqlite3.connect('./data/users.db')
                cur = conn.cursor()
                cur.execute('''UPDATE ip SET address=? WHERE id=?''',
                            (value, _id))
                conn.commit()
                conn.close()
        return jsonify({"openid": orderID}), 200


@app.route('/check', methods=['GET'])
def check():
    with open('./data/check.json', 'r', encoding='utf8') as f:
        check_result = json.load(f)
    return jsonify(check_result), 200


@app.route('/diancan', methods=['GET', 'POST'])
def diancan():
    '''
    利用点餐群 获取夜班信息
    '''
    if request.method == 'GET':
        n = request.args.get('n')  #名字
        d = request.args.get('d')  #点
        d_time = int(time.time())
        if n and d:
            '''
            保存点餐信息
            '''
            # with open('./data/chifan.txt', 'a', encoding='utf8') as f:
            #     f.write(f'{n},{d},{d_time}\n')
            conn = sqlite3.connect('./data/diancan.db')
            c = conn.cursor()
            c.execute(
                '''INSERT INTO diancan (name,diancan,time) VALUES (?,?,?)''',
                (n, d, d_time))
            conn.commit()
            conn.close()
            return jsonify({"respdata": "success"}), 200
        else:
            return jsonify({"respdata": "error"}), 200
    elif request.method == 'POST':
        data = request.get_json()
        if not data:
            return jsonify({"error": "No JSON data received"}), 400
        name = data.get('name')
        # diancan = data.get('diancan')
        # d_time = int(time.time())
        if name:
            '''
            读取点餐信息
            '''
            now_time = int(time.time())
            # 将datetime对象转换为时间戳
            timestamp_15 = time_to_timestamp(hour=15)
            timestamp_before_15 = time_to_timestamp(hour=15, step=-1)
            timestamp_before_23 = time_to_timestamp(hour=23, step=-1)
            timestamp_9 = time_to_timestamp(hour=9)
            timestamp_8 = time_to_timestamp(hour=8)
            # timestamp_0 = int(date_object_23.timestamp())
            conn = sqlite3.connect('./data/diancan.db')
            # print(timestamp_16, timestamp_9, timestamp_0, now_time)
            rf = ''
            if now_time > timestamp_15:
                print('晚')
                # timestamp = timestamp_16
                sqltxt = '''SELECT name,diancan,time FROM diancan WHERE time>? AND (diancan LIKE "%晚%" or diancan LIKE "%早%") ORDER BY TIME DESC'''
                rf = conn.execute(sqltxt, (timestamp_15, ))
            elif now_time < timestamp_9:
                print('早')
                sqltxt = '''SELECT name,diancan,time FROM diancan WHERE time>? AND time<? AND (diancan LIKE "%晚%" or diancan LIKE "%早%") ORDER BY TIME DESC'''
                rf = conn.execute(sqltxt, (
                    timestamp_before_15,
                    timestamp_8,
                ))
            if rf:
                return jsonify({"respdata": str(rf.fetchall())}), 200
            else:
                return jsonify({"respdata": "error"}), 200


@app.route('/qq', methods=['GET', 'POST'])
def qq():
    '''
    QQ用的函数
    '''
    if request.method == 'GET':
        comID = request.args.get('i')
        if comID:
            with open('./data/config.json', 'r', encoding='utf8') as f:
                j = json.load(f)
                allowed_ids = j['allowed_ids']
            for allowed_id in allowed_ids:
                if comID == allowed_id['id']:
                    result = []
                    conn = sqlite3.connect(db2_path)
                    cursor = conn.cursor()
                    sql = "SELECT sysw,sqsw,time FROM qzcs order by time desc limit 1"
                    cursor.execute(sql)
                    result.append(cursor.fetchone())
                    conn.close()
                    conn = sqlite3.connect(r'D:\lljdata\swdb.db')
                    cursor = conn.cursor()
                    sql = 'SELECT sysw,xysw,time FROM swdb WHERE name="刘老涧抽水二站" order by time desc limit 1'
                    cursor.execute(sql)
                    result.append(cursor.fetchone())
                    conn.close()
                    if result:
                        return jsonify({"respdata": result}), 200
                    else:
                        return jsonify({"respdata": "error"}), 200
            return jsonify({"respdata": "fobbiden"}), 403
    else:
        return jsonify({"respdata": "fobbiden"}), 403


# 报警大于warning_time_flag秒再保存
warning_time_flag = 60
warning_time = int(time.time())


@app.route('/warning', methods=['GET', 'POST'])
def warning():
    '''
    获取报警信息,存入warning.db
    '''
    global warning_time
    global publish_flag
    global publish_name
    global publish_type
    if request.method == 'GET':
        publish_name = request.args.get('n')
        publish_type = request.args.get('a')
        now_time = int(time.time())
        if publish_name:
            publish_flag = True
            print(publish_name, publish_type)
            if (now_time - warning_time > warning_time_flag):
                # print(publish_name, a_type)
                warning_time = int(time.time())
                conn = sqlite3.connect('./data/warning.db')
                cur = conn.cursor()
                sqltxt = '''INSERT INTO warning(station,time,msg) VALUES(?,?,?)'''
                cur.execute(sqltxt,
                            (publish_name, int(time.time()), publish_type))
                conn.commit()
                conn.close()
            return jsonify({"respdata": publish_name}), 200
        return jsonify({"respdata": "no parameter"}), 200
    elif request.method == 'POST':
        # publish_flag = True
        # publish_type = 'test'
        data = request.get_json()
        if not data:
            return jsonify({"respdata": "No JSON data received"}), 400
        station = data.get('station')
        # print(type(station), station)
        if station is not None:
            if station == 1:
                station = 's1gate'
            elif station == 2:
                station = 's2gate'
            else:
                station = 'test'
            # print(station)
            conn = sqlite3.connect('./data/warning.db')
            cur = conn.cursor()

            now_time = int(time.time())
            timestamp_0 = time_to_timestamp(hour=0)
            timestamp_9 = time_to_timestamp(hour=9)
            timestamp_16 = time_to_timestamp(hour=16)
            timestamp_before_16 = time_to_timestamp(hour=16, step=-1)
            if now_time > timestamp_16:
                '''
                当前时间超过16点
                '''
                sqltxt = '''SELECT time FROM warning WHERE station=? AND time>?'''
                rf = conn.execute(sqltxt, (station, timestamp_16))
            elif now_time <= timestamp_16 and now_time >= timestamp_9:
                '''
                当前时间在9点-16点
                '''
                sqltxt = '''SELECT time FROM warning WHERE station=? AND time>?'''
                rf = conn.execute(sqltxt, (station, timestamp_0))
                # return jsonify({"respdata": "outtime"}), 200
            elif now_time < timestamp_9 and now_time > timestamp_before_16:
                '''
                当前时间在前一天16点-今天9点
                '''
                sqltxt = '''SELECT time FROM warning WHERE station=? AND time>? AND time<?'''
                rf = conn.execute(sqltxt,
                                  (station, timestamp_before_16, timestamp_9))
            if rf:
                return jsonify({"respdata": str(rf.fetchall())}), 200
            else:
                return jsonify({"respdata": "error"}), 200
        else:
            return jsonify({"respdata": "no parameter"}), 200


@app.route("/software", methods=['GET', 'POST'])
def software():
    if request.method == 'GET':
        return jsonify({"respdata": "ok"}), 200
    if request.method == 'POST':
        data = request.get_json()
        if not data:
            return jsonify({"respdata": "No JSON data received"}), 400
        else:
            software_name = data['name']
            if software:
                return jsonify({"respdata": "ok"}), 200

    return jsonify({"respdata": "error"}), 400

    # i = 0
    # while 1:
    #     i += 1
    #     # print('mqtt flag:', publish_flag)
    #     if publish_flag:
    #         myMqttClient.mqtt_publish("warning", f"{publish_type}")
    #         publish_flag = False
    #     else:
    #         time.sleep(0.1)


@app.route('/upload', methods=['GET', 'POST'])
def upload():
    '''
    上传文件接口
    '''
    if request.method == 'POST':
        if 'file' not in request.files:
            return jsonify({"respdata": "No file part"}), 400
        file = request.files['file']
        if file.filename == '':
            return jsonify({"respdata": "No selected file"}), 400
        else:
            '''
            获取用户openid
            '''
            user = request.form.get('user')
            print('调用拍照：', user)
            start_time = time.time()
            conn = sqlite3.connect('./data/users.db')
            cur = conn.cursor()
            sql = "SELECT id FROM users WHERE openid=?"
            cur.execute(sql, (user, ))
            if cur.fetchone():
                filepath = f'./wximage/{user}'
                if not os.path.exists(filepath):
                    os.mkdir(filepath)
                filepath += '/' + user + os.path.splitext(file.filename)[-1]
                # 尝试保存文件
                try:
                    file.save(filepath)
                    ocrtext, imgbase64 = ocr(filepath, user)
                    # print(ocrtext)
                    # print(imgbase64)
                    return jsonify({
                        "respdata": [ocrtext, str(imgbase64)],
                    }), 200
                except Exception as e:
                    print(e)
                    # 处理保存文件时可能出现的异常
                    return jsonify({
                        "respdata": "Error occurred while saving the file.",
                        "error": str(e)
                    }), 500
                finally:
                    print("用时:", time.time() - start_time, "秒")
            else:
                return jsonify({"respdata": "fobbiden"}), 403
            # if user in
            # 为上传的文件生成一个唯一的文件名，避免重名覆盖
            # filename = os.path.join(app.config['UPLOAD_FOLDER'],
            #                         os.urandom(24).hex() + os.path.splitext(file.filename)[-1])

            # return jsonify({"respdata": "ok"}), 200


if __name__ == "__main__":
    th1 = Thread(target=watchdog, args=(20, ), daemon=True)
    th1.start()
    th2 = Thread(target=mqtt_run,
                 args=('服务器', [("software", 0), ("s1gate", 0), ("s2gate", 0)]),
                 daemon=True)
    th2.start()
    server = pywsgi.WSGIServer(('0.0.0.0', 5000), app)
    server.serve_forever()
